Create a database mapping template

This section contains instructions on how to create mapping templates to load data from various databases. Template management options are available in the Advanced section of the Database Batch Import screen (see The Database Batch Import screen).

Option	Description
New Template	Creates a new template.
Edit Template	Opens a window for template editing.
Rename Template	Opens a dialog where you can specify a new name for the selected template.
Delete Template	Deletes the selected template.
Import Template	Imports the template from a Workflow Template Document.
Export Template	Exports the template into a Workflow Template Document.

To create a new template, select the New Template option from the menu. In the New Template dialog, enter the name in the New Name field. To copy mappings from an existing template, check the Copy from box and select the source template from the list.

To open the new template for editing, select the Edit Template option from the menu. The mapping template window contains several tabs which are described below.

This tab is used to connect to the database from which data will be loaded. Click on Change to specify the database connection settings in the Connection Properties window. If you have any questions, contact your systems administrator. Once the connection has been set up, return to this screen and click on Connect to access the data. You need to be connected to make changes in other parts of the template.

This tab is used to specify parameters for database queries that will be used to import data. An example is shown below.

The Query Parameters tab

This tab is used to map project properties. First, you need to retrieve data from the database. Enter a query in the text field at the top of the tab and click on Execute. If the query has been run successfully, all fields will become active.

In the Project Name field, select the column where the project names are stored.
In the Table Format section, select how your data are organized. Select the Column per Project Property option if your column headers are your project properties (see the image below).

Select the Project property data in one column (multiple rows) option if your data are structured as shown in the image below. In this case you need to specify two more settings: the name of the database column which contains item names (Name Column), and the name of the database column which contains item values (Value Column).
Then you need to map project properties. The Project Property Name column lists properties created for projects in CASH (see Document Property Definitions). Select the column where that property is stored in the database in the Project Property Source Column column.
The Display Property Name table is used to create and map properties by which projects will be organized before loading; it will create consolidation nodes when data are loaded into CASH.

You can organise projects by several properties at once, for example, by country and region. To add a property, click on an empty row at the bottom of the table. To remove a property, click on next to it. The first display property on the list will be applied first. Note that the order of properties cannot be changed so you need to create them in the same order in which you want to apply them.

This tab is used to map project settings. All fields are explained in the table below.

First, you need to retrieve data from the database. Enter a query in the text field at the top of the tab and click on Execute. The fields on the tab will be filled with database column names where appropriate.

Field	Description
Project Name	Select the column where project names are stored.
Periodicity	Select the periodicity of imported data. If you select Mixed, more fields will be shown where you can specify the settings.
Scenario Weighting – Name Column	Select the column which contains the name of the project scenario to which weighting should be applied.
Scenario Weighting – Value Column	Select the column which contains the weighting percentage which should be applied to the selected scenario.
Table Format	Select how your data are organized (see above).
Start Year	Select a column or choose the automatically calculated <First Value Date>.
Duration	Select a column or choose the automatically calculated <Distance to Last Value Date>.
Turn on Sensitivities and Weightings	Select a column.
Escalation/Inflation Date	Select a column.

This tab is used to map variables to columns in the database. If you do not want to import variable values, you can skip this step by unchecking the Apply Variable Mappings box; in this case only project properties and settings will be imported.

If you decide to import variables, the first step is to retrieve data from the database. Enter a query in the text field at the top of the tab and click on Execute. If the query has been executed successfully, fields in the Mappings section will become active (see the table below).

Field	Description
Project Name	Select the column where project names are stored.
Period	Select a column. Periods can be specified as month/year or just year.
Project Scenario	Select a column or type a value.
Table Format	Select how your data are organized (see above).
Regime	Select which Regime will be used to create CASH projects.

The table at the bottom is used to map variables. The Variable Name column contains variables taken from the selected Regime; click on the "+" sign to open the list of all available variables. Then in the Variable Source Column column, select which item in the database corresponds to the chosen variable. If you selected the Column per Variable option above, then the source cells will be filled with database column names. If you selected the Variable data in one column (multiple rows) option, then the source cells will be filled with values from the column selected in the Name Column field.

Values in columns can be filtered. Move the mouse pointer over the column name and the filter button will be displayed. Click on it to view available options. You can create simple custom filters as well.

This tab is used to map working interest shares. If you do not want to import these values, you can skip this step by unchecking the Apply Working Interest Mappings box.

If you decide to import working interest shares, the first step is to retrieve data from the database. Enter a query in the text field at the top of the tab and click on Execute. If the query has been executed successfully, fields in the Mappings section will become active (see the table below).

Field	Description
Project Name	Select the column where project names are stored.
Primary Partner	Select the partner whose results you want to see. Shares of other partners will be summed up and loaded as the remainder.
Remainder	Select the partner who will own the remainder of the working interest.
Is WI TimeSeries?	Check this box if your WI values are in the TimeSeries format; in this case they will be loaded as reversions.
Period	If you check the Is WI TimeSeries? box, this field will become active. Select the column which contains periods for WI values.
Table Format	Select how your data are organized (see above).

The table at the bottom is used to map working interest shares. The Working Interest Category column contains WI categories taken from CASH variable settings. Then in the Working Interest Source Column column, select where WI values for this category are stored in the database. If you selected the Column per Variable option above, then the source cells will be filled with database column names. If you selected the Variable data in one column (multiple rows) option, then the source cells will be filled with values from the column selected in the Name Column field.